'''
Created on 2010-08-14

'''

import re
import sqlite3
import os.path

class sqlloader(object):
    '''A class for writing parsed beerstore/lcbo data to an sqllite3 database.
    Parsed data can be appended to an already existing database, so if you have
    created a database of beerstore data you can then append lcbo data to this
    database later. The opposite is true as well. The one caveat is that if you
    have a beerstore database you cannot append beerstore data. This will 
    overwrite existing beerstore data as all the beerstore tables will be 
    dropped and recreated. The same is true if you have lcbo data and a 
    lcbodata base.
    
    '''
     
    def __init__(self,path):
        self.path = path
            
    def setupBeerDatabase(self):
        '''Create the beerstore database'''
        
        path = self.path
        #apparently os.path.exists() will return false on some systems
        #where os.stat() is not allowed to be called on the path so just 
        #keep this in mind.
        
        self.conn = sqlite3.connect(path) #:connection to the sql database.
        #self.conn = sqlite3.connect(':memory:')
        self.cursor = self.conn.cursor()
        
        #always overwrite any existing database
        if os.path.isfile(path):
            self.cursor.execute('drop table if exists beers')
            self.cursor.execute('drop table if exists beerinfo')
            self.cursor.execute('drop table if exists pricing')
            self.cursor.execute('drop table if exists allSizes')
            self.conn.commit()
        
        self.createMainTable()    
    
    def setupLcboDatabase(self):
        '''Create the lcbo database'''
        
        path = self.path
        
        self.conn = sqlite3.connect(path) #:connection to the sql database.
        self.cursor = self.conn.cursor()
        
        #drop the tables if they exist so we can recreate them.
        if os.path.isfile(path):
            self.cursor.execute('drop table if exists lcitems')
            self.cursor.execute('drop table if exists slccat')
            self.cursor.execute('drop table if exists lcdesc')
            self.cursor.execute('drop table if exists lccompany')
            self.conn.commit()
            
        #each item has its own id
        self.id = 0 
        '''the current value of this variable denotes the 
        next available id number. used only wrt to the lcbo database'''
        
        #each item may have multiple different sizes, brewers, categories etc
        #thus each time we are dealing with the same item but there is a slight
        #difference in its description (i.e quantity) we give this instance a
        #separate sub id. so for instance bud light 3x355 has id 1 subid 1 but
        #bud light 1x710 has id 1 but sudid 2.
        self.subid = 0
        '''a subid used for items of the same brand but differing character,
        i.e budweiser 3x355ML vs budweiser 1x1180ML. used only wrt to the 
        lcbo database'''
        
        self.idMap = {} #:mapping that maps an item name to its id.
        self.subIdMap = {} #:mapping that keeps track of the current subid num.
        
        self.createLcboTables()
        
    def createMainTable(self):
        '''Create the tables necessary for the beerstore database'''
        
        categoryTable = '''create table beers (name text,''' +\
        ''' brewer text, category text, primary key (name,brewer))'''
        
        beerTable = '''create table beerinfo (name text, brewer text,''' + \
        ''' alcohol float, type text, description text, splashlink text''' +\
        ''', primary key (name,brewer))'''     
        
        pricingTable = '''create table pricing (name text,''' + \
        ''' quantity integer,''' + \
        ''' size integer, type text, ''' + \
        '''price float, storeURL text, brewer text)''' 
        
        
        allSizesTable = '''create table allSizes(name text,''' +\
        ''' allSizesURL text, brewer text, primary key (name, brewer))'''
        try:
            self.cursor.execute(categoryTable)
            self.cursor.execute(beerTable)
            self.cursor.execute(pricingTable)
            self.cursor.execute(allSizesTable)
        except sqlite3.OperationalError:
            print 'Path supplied pointed to non database file. Exiting.'
            exit()
    
    def createLcboTables(self):
        '''Create the tables necessary for the lcbo database'''
        
        LidTable = '''create table lcitems (id integer, name text,'''+\
        ''' primary key (id), unique(name))'''
        
        LcatTable = '''create table lccat (id integer, subid integer,'''+\
        ''' cat text, subcat text, subcat2 text, foreign key (id) '''+\
        '''references lcitems(id), primary key (id,subid))'''
        
        LdTable = '''create table lcdesc (id integer, subid integer, '''+\
        '''quantity integer, size integer, type text, alcohol float, price'''+\
        ''' float, foreign key (id) references lcitems(id),'''+\
        ''' primary key (id,subid))'''
        
        LcTable = '''create table lccompany (id integer, subid integer,'''+\
        ''' location text, company text, foreign key (id) '''+\
        '''references lcitems(id), primary key (id,subid))'''
        
        try:
            self.cursor.execute(LidTable)
            self.cursor.execute(LcatTable)
            self.cursor.execute(LdTable)
            self.cursor.execute(LcTable)
        except sqlite3.OperationalError:
            print 'Path supplied pointed to non database file. Exiting.'
            exit()
        
    def addLcboItem(self,item):
        '''Add an lcbo item into the lcbo database
        @param item: an lcbo item
        @type item: lcboitem
        
        '''
        
        #if we have not catalogued the item before add it now.
        if item.title not in self.idMap:
            self.idMap[item.title] = self.id
            self.id = self.id + 1
            self.subIdMap[item.title] = 0 #first item added has subid of zero.
            lcitemsV = (self.idMap[item.title],item.title)
            self.cursor.execute('insert into lcitems values (?,?)', lcitemsV)
        else:
            #if this item already exists then we are adding the same item but
            #with different details.
            self.subIdMap[item.title] = self.subIdMap[item.title] + 1
            
        id = self.idMap[item.title]
        subId = self.subIdMap[item.title]
        
        #categories usually come either like 'wine, red wine, tasty wine'
        #or just 'wine'. I have only seen up to 2 sub categories.
        if item.category.find(',') == -1:
            lcCatV = (id,subId,item.category,None,None)
        else:
            cats = item.category.split(',')
        
            if len(cats) == 3:
                cat = cats[0].strip()
                subcat = cats[1].strip()
                subcat2 = cats[2].strip()
            elif len(cats) == 2:
                cat = cats[0].strip()
                subcat = cats[1].strip()
                subcat2 = None
            else:
                print 'Category is bogus for item #' + `self.id`\
                 + ' ' + item.title + ' cat is:' + item.category 
        
            lcCatV = (id,subId,cat,subcat,subcat2)
        
        lcDescV = (id,subId,item.amount,item.size,item.type,
                   item.percentage,item.price)  
        
        lcCompanyV = (id,subId,item.geography,item.brewer)
            
        self.cursor.execute('insert into lccat values (?,?,?,?,?)',lcCatV)
        self.cursor.execute('insert into lcdesc values (?,?,?,?,?,?,?)', lcDescV)
        self.cursor.execute('insert into lccompany values (?,?,?,?)', lcCompanyV)
        
             
    def addBeer(self, beer):
        '''Add a beer to the beer database
        @param beer: A single beer
        @type beer: beerInfo
        '''
        self.cursor.execute('insert into beerinfo values (?,?,?,?,?,?)', 
                        (beer.getName(),
                        beer.getBrewer(),
                        beer.getAlcoholLevel(),
                        beer.getType(),
                        beer.getDescription(),
                        beer.getLocationLink())
                        )
                                
        self.cursor.execute('insert into beers values (?,?,?)',
                            (beer.getName(), 
                             beer.getBrewer(),
                             beer.getCategory())
                            )
    
    def addPricing(self, beer):
        '''Add pricing information for a beer to the beer database
        @param beer: a beer
        @type beer: beerInfo
        
        '''
        
        pricing,allSizesUrl = beer.pricingInfo()
               
        self.addAllPricesURL(allSizesUrl, beer)
        
        #This beer has no pricing information.
        if pricing == None: return
        
        for quantityDesc,price,location in pricing:
            numVals = re.findall('''(\d+ )''', quantityDesc)
            
            quantity = numVals[0].strip() #quantity of cans or bottles 
            quantity = int(quantity)
            unitSize = numVals[1].strip() #unit size eg 341ml 355ml etc.
            unitSize = int(unitSize)
            
            #parse the container type. is it a can or a bottle etc?
            containerType = re.search('''\D+''',quantityDesc).group(0).strip()
            
            #keg sizes are listed in terms of litres but our units should be 
            #in ml. 
            if (containerType == 'Keg' or containerType == 'keg'):
                unitSize = unitSize * 1000
                 
            self.cursor.execute('insert into pricing values (?,?,?,?,?,?,?)', 
                                (beer.getName(),
                                quantity,
                                unitSize,
                                containerType,
                                float(price),
                                location,
                                beer.getBrewer())
                                ) 
            
    
    def addAllPricesURL(self, url, beer):
        '''Adds the 'addAllPricesURL' for a beer item to the database
        @param url: the 'addAllPricesURL'
        @type url: string
        
        @param beer: a beer
        @type beer: beerInfo
        
        '''
        if url == None: return
        
        self.cursor.execute('insert into allSizes values (?,?,?)', 
                            (beer.getName(),url,beer.getBrewer()))
                
    def dumpTable(self):
        '''Print the beer database to stdout'''
        self.conn.commit()
        
        self.cursor.execute('select * from beer')
        
        for row in self.cursor:
            print row
        print
        
        self.cursor.execute('select * from pricing')
        for row in self.cursor:
            print row
        print
        
        self.cursor.execute('select * from allSizes')
        for row in self.cursor:
            print row
        print
        
        self.cursor.execute('select * from category')
        for row in self.cursor:
            print row
        print 

    def close(self):
        '''Close the connection to the sqlite3 database'''
        self.conn.commit()
        self.cursor.close()
        
#    conn = sqlite3.connect(':memory:')
#    c = conn.cursor()
#    c.execute('''create table beer (name text, content float)''')
#    c.execute("""insert into beer values ('BUSCH', 4.0)""")
#    conn.commit()
#    c.execute('select * from beer')
#    
#    for row in c:
#        print  row

if __name__ == '__main__':
    sql = sqlloader()
    sql.setupLcboDatabase()
    sql.createLcboTables()